package com.waypolice.manager.controller;

import com.waypolice.base.BaseController;
import com.waypolice.common.utils.DateTimeUtil;
import com.waypolice.common.utils.FileUtils;
import com.waypolice.mapper.CmsJudItemMapper;
import com.waypolice.mapper.CmsJudVolumeMapper;
import com.waypolice.mapper.CmsSinItemMapper;
import com.waypolice.pojo.*;
import io.swagger.annotations.Api;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.shiro.SecurityUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * @Description  导出数据
 * @Author wangrang
 * @Date Created in 13:31 2019/4/12
 * @Modified By:
 */
@Controller
@Api(value = "导出数据", description = "数据导出操作")
@RequestMapping(value="/pcwindow")
public class ExportAction extends BaseController {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExportAction.class);

    @Autowired
    private CmsJudVolumeMapper cmsJudVolumeMapper;

    @Autowired
    private CmsJudItemMapper cmsJudItemMapper;

    @Autowired
    private CmsSinItemMapper cmsSinItemMapper;

    /**
     * 将生成号的sheets写入到文件，并调用文件下载方法
     * @param request
     * @param response
     * @param sheets
     * @param name
     */
    private void writeOutStream(HttpServletRequest request, HttpServletResponse response, SXSSFWorkbook sheets, String name) {
        String path = FileUtils.projectWay();
        File file = new File(path + name);
        try {
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            sheets.write(fileOutputStream);
            responseMethod(request,response,name,file);
            fileOutputStream.close();
            fileOutputStream.flush();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }catch (IOException e) {
            e.printStackTrace();
        }finally {
            file.delete();
        }
    }

    /**
     * 下载文件工具类
     */
    public void responseMethod(HttpServletRequest request, HttpServletResponse response, String name, File file) {
        String agent = request.getHeader("USER-AGENT");
        String fileName = null;
        try {
            //针对IE或者以IE为内核的浏览器：
            if (agent.contains("MSIE") || agent.contains("Trident")) {
                fileName = java.net.URLEncoder.encode(name, "UTF-8");
            } else {
                //非IE浏览器的处理：
                fileName = new String(name.getBytes("utf-8"), "iso-8859-1");
            }

        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setContentType("application/octet-stream");
        response.setHeader("Content-Disposition", "attchement;filename=" + fileName);
        OutputStream out = null;
        InputStream in = null;
        try {
            in = new FileInputStream(file);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        try {
            byte[] bytes = new byte[5120];
            out = new BufferedOutputStream(response.getOutputStream());
            int len = 0;
            while ((len = in.read(bytes)) != -1) {
                out.write(bytes, 0, len);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                out.flush();
                in.close();
                out.close();
                //file.delete   ();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 传统卷件数据导出
     * @param request
     * @param response
     */
    @RequestMapping("/exportVolAndItem")
    public void importOrderShanxi(HttpServletRequest request, HttpServletResponse response,
                                  String volTitle,String volNo, String volArchiveStatus) {
        CmsJudVolumeExample cmsJudVolumeExample = new CmsJudVolumeExample();
        CmsJudVolumeExample.Criteria criteria = cmsJudVolumeExample.createCriteria();
        if(StringUtils.isNotBlank(volTitle)){
            criteria.andVolTitleLike("%"+volTitle+"%");
        }
        if(StringUtils.isNotBlank(volNo)){
            criteria.andVolNoEqualTo(volNo);
        }
        if(StringUtils.isNotBlank(volArchiveStatus) && "0".equals(volArchiveStatus)){
            criteria.andVolArchiveStatusEqualTo(0);
        }else if(StringUtils.isNotBlank(volArchiveStatus) && "1".equals(volArchiveStatus)){
            criteria.andVolArchiveStatusEqualTo(1);
        }else if(StringUtils.isNotBlank(volArchiveStatus) && "2".equals(volArchiveStatus)){
            criteria.andVolArchiveStatusEqualTo(2);
        }else if(StringUtils.isNotBlank(volArchiveStatus) && "3".equals(volArchiveStatus)){
            criteria.andVolArchiveStatusEqualTo(3);
        }else if(StringUtils.isNotBlank(volArchiveStatus) && "4".equals(volArchiveStatus)){
            criteria.andVolArchiveStatusEqualTo(4);
        }
        cmsJudVolumeExample.setOrderByClause("\"VOL_NO\" DESC");
        List<CmsJudVolume> cmsJudVolumes = cmsJudVolumeMapper.selectByExample(cmsJudVolumeExample);
        List<CmsJudItem> cmsJudItemsAll = new ArrayList<>();
        for (CmsJudVolume cmsJudVolume : cmsJudVolumes) {
            Long volId = cmsJudVolume.getVolId();
            CmsJudItemExample cmsJudItemExample = new CmsJudItemExample();
            CmsJudItemExample.Criteria criteria1 = cmsJudItemExample.createCriteria();
            criteria1.andItmeVolIdEqualTo(volId);
            cmsJudItemExample.setOrderByClause("\"ITEM_NUM\" ASC");
            List<CmsJudItem> cmsJudItems = cmsJudItemMapper.selectByExample(cmsJudItemExample);
            cmsJudItemsAll.addAll(cmsJudItems);
        }
        importExcel(request,response,cmsJudVolumes,cmsJudItemsAll);
    }

    /**
     * 业务订单导出工具类
     * @param request
     * @param response
     * @param
     */
    public void importExcel(HttpServletRequest request, HttpServletResponse response,List<CmsJudVolume> cmsJudVolumes,
                            List<CmsJudItem> cmsJudItemsAll) {
        SXSSFWorkbook sheets = new SXSSFWorkbook();

        SXSSFSheet sheet = sheets.createSheet("Volume");
        //第一行
        SXSSFRow row0 = sheet.createRow(0);
        SXSSFCell volId = row0.createCell(0);
        volId.setCellValue("ID");
        SXSSFCell volClass = row0.createCell(1);
        volClass.setCellValue("案卷类型");
        SXSSFCell volStorageTime = row0.createCell(2);
        volStorageTime.setCellValue("保管期限");
        SXSSFCell volNo = row0.createCell(3);
        volNo.setCellValue("案卷号");
        SXSSFCell volFian = row0.createCell(4);
        volFian.setCellValue("全宗号");
        SXSSFCell volTitle = row0.createCell(5);
        volTitle.setCellValue("案卷题名");
        SXSSFCell volArchiveYear = row0.createCell(6);
        volArchiveYear.setCellValue("归档年度");
        SXSSFCell volArchiveUnit = row0.createCell(7);
        volArchiveUnit.setCellValue("归档单位");
        SXSSFCell volAuditLeader = row0.createCell(8);
        volAuditLeader.setCellValue("审计组长");
        SXSSFCell volAuditDate = row0.createCell(9);
        volAuditDate.setCellValue("审计起止日期");
        SXSSFCell volSecret = row0.createCell(10);
        volSecret.setCellValue("密级");
        SXSSFCell volInsideDate = row0.createCell(11);
        volInsideDate.setCellValue("卷内起止日期");
        SXSSFCell volTotal = row0.createCell(12);
        volTotal.setCellValue("总卷数");
        SXSSFCell volCurrent = row0.createCell(13);
        volCurrent.setCellValue("当前卷数");
        SXSSFCell volCaseNum = row0.createCell(14);
        volCaseNum.setCellValue("档号");
        SXSSFCell volTotalPage = row0.createCell(15);
        volTotalPage.setCellValue("页数");
        SXSSFCell volArchiveDate = row0.createCell(16);
        volArchiveDate.setCellValue("归档日期");
        SXSSFCell volCarrier = row0.createCell(17);
        volCarrier.setCellValue("载体类型(0:仅实体;1:仅电子;2:共存)");
        SXSSFCell volItemNumber = row0.createCell(18);
        volItemNumber.setCellValue("件数");
        SXSSFCell volBoxNumber = row0.createCell(19);
        volBoxNumber.setCellValue("盒编号");
        SXSSFCell volStoragelocation = row0.createCell(20);
        volStoragelocation.setCellValue("存放位置");
        SXSSFCell volArchivePerson = row0.createCell(21);
        volArchivePerson.setCellValue("归档责任人");
        SXSSFCell volStatus = row0.createCell(22);
        volStatus.setCellValue("案卷状态(0:正常;1:鉴定中;2:借阅中;3:已移出;8:销毁;9:删除)");
        SXSSFCell volPublicStatus = row0.createCell(23);
        volPublicStatus.setCellValue("是否公开(0:未公开(默认);1:公开)");
        SXSSFCell volStr1 = row0.createCell(24);
        volStr1.setCellValue("目录号");
        SXSSFCell volStr2 = row0.createCell(25);
        volStr2.setCellValue("著录人");
        SXSSFCell volCreateDate = row0.createCell(26);
        volCreateDate.setCellValue("创建时间");
        SXSSFCell volStr3 = row0.createCell(27);
        volStr3.setCellValue("保密年限");
        SXSSFCell volStr2Date = row0.createCell(28);
        volStr2Date.setCellValue("著录时间");
        //第二行
        SXSSFRow row1 = sheet.createRow(1);
        SXSSFCell volIdByTwo = row1.createCell(0);
        volIdByTwo.setCellValue("volId");
        SXSSFCell volClassByTwo = row1.createCell(1);
        volClassByTwo.setCellValue("volClass");
        SXSSFCell volStorageTimeByTwo = row1.createCell(2);
        volStorageTimeByTwo.setCellValue("volStorageTime");
        SXSSFCell volNoByTwo = row1.createCell(3);
        volNoByTwo.setCellValue("volNo");
        SXSSFCell volFianByTwo = row1.createCell(4);
        volFianByTwo.setCellValue("volFian");
        SXSSFCell volTitleByTwo = row1.createCell(5);
        volTitleByTwo.setCellValue("volTitle");
        SXSSFCell volArchiveYearByTwo = row1.createCell(6);
        volArchiveYearByTwo.setCellValue("volArchiveYear");
        SXSSFCell volArchiveUnitByTwo = row1.createCell(7);
        volArchiveUnitByTwo.setCellValue("volArchiveUnit");
        SXSSFCell volAuditLeaderByTwo = row1.createCell(8);
        volAuditLeaderByTwo.setCellValue("volAuditLeader");
        SXSSFCell volAuditDateByTwo = row1.createCell(9);
        volAuditDateByTwo.setCellValue("volAuditDate");
        SXSSFCell volSecretByTwo = row1.createCell(10);
        volSecretByTwo.setCellValue("volSecret");
        SXSSFCell volInsideDateByTwo = row1.createCell(11);
        volInsideDateByTwo.setCellValue("volInsideDate");
        SXSSFCell volTotalByTwo = row1.createCell(12);
        volTotalByTwo.setCellValue("volTotal");
        SXSSFCell volCurrentByTwo = row1.createCell(13);
        volCurrentByTwo.setCellValue("volCurrent");
        SXSSFCell volCaseNumByTwo = row1.createCell(14);
        volCaseNumByTwo.setCellValue("volCaseNum");
        SXSSFCell volTotalPageByTwo = row1.createCell(15);
        volTotalPageByTwo.setCellValue("volTotalPage");
        SXSSFCell volArchiveDateByTwo = row1.createCell(16);
        volArchiveDateByTwo.setCellValue("volArchiveDate");
        SXSSFCell volCarrierByTwo = row1.createCell(17);
        volCarrierByTwo.setCellValue("volCarrier");
        SXSSFCell volItemNumberByTwo = row1.createCell(18);
        volItemNumberByTwo.setCellValue("volItemNumber");
        SXSSFCell volBoxNumberByTwo = row1.createCell(19);
        volBoxNumberByTwo.setCellValue("volBoxNumber");
        SXSSFCell volStoragelocationByTwo = row1.createCell(20);
        volStoragelocationByTwo.setCellValue("volStoragelocation");
        SXSSFCell volArchivePersonByTwo = row1.createCell(21);
        volArchivePersonByTwo.setCellValue("volArchivePerson");
        SXSSFCell volStatusByTwo = row1.createCell(22);
        volStatusByTwo.setCellValue("volStatus");
        SXSSFCell volPublicStatusByTwo = row1.createCell(23);
        volPublicStatusByTwo.setCellValue("volPublicStatus");
        SXSSFCell volStr1ByTwo = row1.createCell(24);
        volStr1ByTwo.setCellValue("volStr1");
        SXSSFCell volStr2ByTwo = row1.createCell(25);
        volStr2ByTwo.setCellValue("volStr2");
        SXSSFCell volCreateDateByTwo = row1.createCell(26);
        volCreateDateByTwo.setCellValue("volCreateDate");
        SXSSFCell volStr3ByTwo = row1.createCell(27);
        volStr3ByTwo.setCellValue("volStr3");
        SXSSFCell volStr2DateByTwo = row1.createCell(28);
        volStr2DateByTwo.setCellValue("volStr2Date");
            //第三行插入数据
        if (cmsJudVolumes.size() != 0) {
            for (int i = 0; i < cmsJudVolumes.size(); i++) {
                CmsJudVolume cmsJudVolume = cmsJudVolumes.get(i);
                SXSSFRow rows = sheet.createRow(i + 2);

                SXSSFCell volIdRows = rows.createCell(0);
                volIdRows.setCellValue(cmsJudVolume.getVolId() == null ? "" :cmsJudVolume.getVolId().toString());
                SXSSFCell volClassRows = rows.createCell(1);
                volClassRows.setCellValue(cmsJudVolume.getVolClass() == null ? "" :cmsJudVolume.getVolClass().toString());
                SXSSFCell volStorageTimeRows = rows.createCell(2);
                volStorageTimeRows.setCellValue(cmsJudVolume.getVolStorageTime() == null ? "" :cmsJudVolume.getVolStorageTime().toString());
                SXSSFCell volNoRows = rows.createCell(3);
                volNoRows.setCellValue(cmsJudVolume.getVolNo() == null ? "" :cmsJudVolume.getVolNo().toString());
                SXSSFCell volFianRows = rows.createCell(4);
                volFianRows.setCellValue(cmsJudVolume.getVolFian() == null ? "" :cmsJudVolume.getVolFian().toString());
                SXSSFCell volTitleRows = rows.createCell(5);
                volTitleRows.setCellValue(cmsJudVolume.getVolTitle() == null ? "" :cmsJudVolume.getVolTitle().toString());
                SXSSFCell volArchiveYearRows = rows.createCell(6);
                volArchiveYearRows.setCellValue(cmsJudVolume.getVolArchiveYear() == null ? "" :cmsJudVolume.getVolArchiveYear().toString());
                SXSSFCell volArchiveUnitRows = rows.createCell(7);
                volArchiveUnitRows.setCellValue(cmsJudVolume.getVolArchiveUnit() == null ? "" :cmsJudVolume.getVolArchiveUnit().toString());
                SXSSFCell volAuditLeaderRows = rows.createCell(8);
                volAuditLeaderRows.setCellValue(cmsJudVolume.getVolAuditLeader() == null ? "" :cmsJudVolume.getVolAuditLeader().toString());
                SXSSFCell volAuditDateRows = rows.createCell(9);
                volAuditDateRows.setCellValue(cmsJudVolume.getVolAuditDate() == null ? "" :cmsJudVolume.getVolAuditDate().toString());
                SXSSFCell volSecretRows = rows.createCell(10);
                volSecretRows.setCellValue(cmsJudVolume.getVolSecret() == null ? "" :cmsJudVolume.getVolSecret().toString());
                SXSSFCell volInsideDateRows = rows.createCell(11);
                volInsideDateRows.setCellValue(cmsJudVolume.getVolInsideDate() == null ? "" :cmsJudVolume.getVolInsideDate().toString());
                SXSSFCell volTotalRows = rows.createCell(12);
                volTotalRows.setCellValue(cmsJudVolume.getVolTotal() == null ? "" :cmsJudVolume.getVolTotal().toString());
                SXSSFCell volCurrentRows = rows.createCell(13);
                volCurrentRows.setCellValue(cmsJudVolume.getVolCurrent() == null ? "" :cmsJudVolume.getVolCurrent().toString());
                SXSSFCell volCaseNumRows = rows.createCell(14);
                volCaseNumRows.setCellValue(cmsJudVolume.getVolCaseNum() == null ? "" :cmsJudVolume.getVolCaseNum().toString());
                SXSSFCell volTotalPageRows = rows.createCell(15);
                volTotalPageRows.setCellValue(cmsJudVolume.getVolTotalPage() == null ? "" :cmsJudVolume.getVolTotalPage().toString());
                SXSSFCell volArchiveDateRows = rows.createCell(16);
                volArchiveDateRows.setCellValue(cmsJudVolume.getVolArchiveDate() == null ? "" :cmsJudVolume.getVolArchiveDate().toString());
                SXSSFCell volCarrierRows = rows.createCell(17);
                volCarrierRows.setCellValue(cmsJudVolume.getVolCarrier() == null ? "" :cmsJudVolume.getVolCarrier().toString());
                SXSSFCell volItemNumberRows = rows.createCell(18);
                volItemNumberRows.setCellValue(cmsJudVolume.getVolItemNumber() == null ? "" :cmsJudVolume.getVolItemNumber().toString());
                SXSSFCell volBoxNumberRows = rows.createCell(19);
                volBoxNumberRows.setCellValue(cmsJudVolume.getVolBoxNumber() == null ? "" :cmsJudVolume.getVolBoxNumber().toString());
                SXSSFCell volStoragelocationRows = rows.createCell(20);
                volStoragelocationRows.setCellValue(cmsJudVolume.getVolStoragelocation() == null ? "" :cmsJudVolume.getVolStoragelocation().toString());
                SXSSFCell volArchivePersonRows = rows.createCell(21);
                volArchivePersonRows.setCellValue(cmsJudVolume.getVolArchivePerson() == null ? "" :cmsJudVolume.getVolArchivePerson().toString());
                SXSSFCell volStatusRows = rows.createCell(22);
                volStatusRows.setCellValue(cmsJudVolume.getVolStatus() == null ? "" :cmsJudVolume.getVolStatus().toString());
                SXSSFCell volPublicStatusRows = rows.createCell(23);
                volPublicStatusRows.setCellValue(cmsJudVolume.getVolPublicStatus() == null ? "" :cmsJudVolume.getVolPublicStatus().toString());
                SXSSFCell volStr1Rows = rows.createCell(24);
                volStr1Rows.setCellValue(cmsJudVolume.getVolStr1() == null ? "" :cmsJudVolume.getVolStr1().toString());
                SXSSFCell volStr2Rows = rows.createCell(25);
                volStr2Rows.setCellValue(cmsJudVolume.getVolStr2() == null ? "" :cmsJudVolume.getVolStr2().toString());
                SXSSFCell volCreateDateRows = rows.createCell(26);//创建时间
                if (cmsJudVolume.getVolCreateDate() != null) {
                    String s = DateTimeUtil.dateToStrLong(cmsJudVolume.getVolCreateDate());
                    volCreateDateRows.setCellValue(s);

                } else {
                    volCreateDateRows.setCellValue("");
                }
                SXSSFCell volStr3Rows = rows.createCell(27);
                volStr3Rows.setCellValue(cmsJudVolume.getVolStr3() == null ? "" :cmsJudVolume.getVolStr3().toString());

                SXSSFCell volStr2DateRows = rows.createCell(28);//著录时间
                if (cmsJudVolume.getVolStr2Date() != null) {
                    String s = DateTimeUtil.dateToStrLong(cmsJudVolume.getVolCreateDate());
                    volStr2DateRows.setCellValue(s);
                } else {
                    volStr2DateRows.setCellValue("");
                }

            }
        }


        SXSSFSheet sheet2 = sheets.createSheet("Item");
        //第一行
        SXSSFRow rowSheet2 = sheet2.createRow(0);
        SXSSFCell itemId = rowSheet2.createCell(0);
        itemId.setCellValue("ID");
        SXSSFCell itmeVolId = rowSheet2.createCell(1);
        itmeVolId.setCellValue("所属卷(卷外键)");
        SXSSFCell itemNum = rowSheet2.createCell(2);
        itemNum.setCellValue("序号");
        SXSSFCell itemName = rowSheet2.createCell(3);
        itemName.setCellValue("文号");
        SXSSFCell itemTemporary = rowSheet2.createCell(4);
        itemTemporary.setCellValue("单元号");
        SXSSFCell itemSecret = rowSheet2.createCell(5);
        itemSecret.setCellValue("密级");
        SXSSFCell itemTitle = rowSheet2.createCell(6);
        itemTitle.setCellValue("题名");
        SXSSFCell itemRespons = rowSheet2.createCell(7);
        itemRespons.setCellValue("责任者");
        SXSSFCell itemCreateTime = rowSheet2.createCell(8);
        itemCreateTime.setCellValue("发文日期");
        SXSSFCell itemPage = rowSheet2.createCell(9);
        itemPage.setCellValue("页次");
        SXSSFCell itemStatus = rowSheet2.createCell(10);
        itemStatus.setCellValue("件状态(0:正常;1:鉴定中;2:借阅中;3:已移出;8:销毁)");
        SXSSFCell itemStr1 = rowSheet2.createCell(11);
        itemStr1.setCellValue("件号");
        SXSSFCell itemStr2 = rowSheet2.createCell(12);
        itemStr2.setCellValue("挂接原文地址");
        SXSSFCell itemStr4 = rowSheet2.createCell(13);
        itemStr4.setCellValue("著录人");
        SXSSFCell itemStr4Date = rowSheet2.createCell(14);
        itemStr4Date.setCellValue("著录时间");
        SXSSFCell itemStr11Date = rowSheet2.createCell(15);
        itemStr11Date.setCellValue("件归档日期");
        SXSSFCell itemCreateDate = rowSheet2.createCell(16);
        itemCreateDate.setCellValue("创建时间");
        SXSSFCell itemStr5 = rowSheet2.createCell(17);
        itemStr5.setCellValue("是否公开(0:未公开(默认);1:公开)");
        SXSSFCell itemStr6 = rowSheet2.createCell(18);
        itemStr6.setCellValue("保密期限(年)");
        SXSSFCell itemVolCaseNum = rowSheet2.createCell(19);
        itemVolCaseNum.setCellValue("案卷档号");

        //第二行
        SXSSFRow rowSheet22 = sheet2.createRow(1);
        SXSSFCell itemId22 = rowSheet22.createCell(0);
        itemId22.setCellValue("itemId");
        SXSSFCell itmeVolId22 = rowSheet22.createCell(1);
        itmeVolId22.setCellValue("itmeVolId");
        SXSSFCell itemNum22 = rowSheet22.createCell(2);
        itemNum22.setCellValue("itemNum");
        SXSSFCell itemName22 = rowSheet22.createCell(3);
        itemName22.setCellValue("itemName");
        SXSSFCell itemTemporary22 = rowSheet22.createCell(4);
        itemTemporary22.setCellValue("itemTemporary");
        SXSSFCell itemSecret22 = rowSheet22.createCell(5);
        itemSecret22.setCellValue("itemSecret");
        SXSSFCell itemTitle22 = rowSheet22.createCell(6);
        itemTitle22.setCellValue("itemTitle");
        SXSSFCell itemRespons22 = rowSheet22.createCell(7);
        itemRespons22.setCellValue("itemRespons");
        SXSSFCell itemCreateTime22 = rowSheet22.createCell(8);
        itemCreateTime22.setCellValue("itemCreateTime");
        SXSSFCell itemPage22 = rowSheet22.createCell(9);
        itemPage22.setCellValue("itemPage");
        SXSSFCell itemStatus22 = rowSheet22.createCell(10);
        itemStatus22.setCellValue("itemStatus");
        SXSSFCell itemStr122 = rowSheet22.createCell(11);
        itemStr122.setCellValue("itemStr1");
        SXSSFCell itemStr222 = rowSheet22.createCell(12);
        itemStr222.setCellValue("itemStr2");
        SXSSFCell itemStr422 = rowSheet22.createCell(13);
        itemStr422.setCellValue("itemStr4");
        SXSSFCell itemStr4Date22 = rowSheet22.createCell(14);
        itemStr4Date22.setCellValue("itemStr4Date");
        SXSSFCell itemStr11Date22 = rowSheet22.createCell(15);
        itemStr11Date22.setCellValue("itemStr11Date");
        SXSSFCell itemCreateDate22 = rowSheet22.createCell(16);
        itemCreateDate22.setCellValue("itemCreateDate");
        SXSSFCell itemStr522 = rowSheet22.createCell(17);
        itemStr522.setCellValue("itemStr5");
        SXSSFCell itemStr622 = rowSheet22.createCell(18);
        itemStr622.setCellValue("itemStr6");
        SXSSFCell itemVolCaseNum22 = rowSheet22.createCell(19);
        itemVolCaseNum22.setCellValue("volCaseNum");
            //第二行插入数据
        if(cmsJudItemsAll.size()!=0){
            for (int i = 0; i < cmsJudItemsAll.size(); i++) {
                CmsJudItem cmsJudItem = cmsJudItemsAll.get(i);
                SXSSFRow rowsSheet2 = sheet2.createRow(i + 2);
                SXSSFCell itemIdRS2 = rowsSheet2.createCell(0);
                itemIdRS2.setCellValue(cmsJudItem.getItemId() == null ? "" :cmsJudItem.getItemId().toString());
                SXSSFCell itmeVolIdRS2 = rowsSheet2.createCell(1);
                itmeVolIdRS2.setCellValue(cmsJudItem.getItmeVolId() == null ? "" :cmsJudItem.getItmeVolId().toString());
                SXSSFCell itemNumRS2 = rowsSheet2.createCell(2);
                itemNumRS2.setCellValue(cmsJudItem.getItemNum() == null ? "" :cmsJudItem.getItemNum().toString());
                SXSSFCell itemNameRS2 = rowsSheet2.createCell(3);
                itemNameRS2.setCellValue(cmsJudItem.getItemName() == null ? "" :cmsJudItem.getItemName().toString());
                SXSSFCell itemTemporaryRS2 = rowsSheet2.createCell(4);
                itemTemporaryRS2.setCellValue(cmsJudItem.getItemTemporary() == null ? "" :cmsJudItem.getItemTemporary().toString());
                SXSSFCell itemSecretRS2 = rowsSheet2.createCell(5);
                itemSecretRS2.setCellValue(cmsJudItem.getItemSecret() == null ? "" :cmsJudItem.getItemSecret().toString());
                SXSSFCell itemTitleRS2 = rowsSheet2.createCell(6);
                itemTitleRS2.setCellValue(cmsJudItem.getItemTitle() == null ? "" :cmsJudItem.getItemTitle().toString());
                SXSSFCell itemResponsRS2 = rowsSheet2.createCell(7);
                itemResponsRS2.setCellValue(cmsJudItem.getItemRespons() == null ? "" :cmsJudItem.getItemRespons().toString());
                SXSSFCell itemCreateTimeRS2 = rowsSheet2.createCell(8);
                itemCreateTimeRS2.setCellValue(cmsJudItem.getItemCreateTime() == null ? "" :cmsJudItem.getItemCreateTime().toString());
                SXSSFCell itemPageRS2 = rowsSheet2.createCell(9);
                itemPageRS2.setCellValue(cmsJudItem.getItemPage() == null ? "" :cmsJudItem.getItemPage().toString());
                SXSSFCell itemStatusRS2 = rowsSheet2.createCell(10);
                itemStatusRS2.setCellValue(cmsJudItem.getItemStatus() == null ? "" :cmsJudItem.getItemStatus().toString());
                SXSSFCell itemStr1RS2 = rowsSheet2.createCell(11);
                itemStr1RS2.setCellValue(cmsJudItem.getItemStr1() == null ? "" :cmsJudItem.getItemStr1().toString());
                SXSSFCell itemStr2RS2 = rowsSheet2.createCell(12);
                itemStr2RS2.setCellValue(cmsJudItem.getItemStr2() == null ? "" :cmsJudItem.getItemStr2().toString());
                SXSSFCell itemStr4RS2 = rowsSheet2.createCell(13);
                itemStr4RS2.setCellValue(cmsJudItem.getItemStr4() == null ? "" :cmsJudItem.getItemStr4().toString());
                SXSSFCell itemStr4DateRS2 = rowsSheet2.createCell(14);
                if (cmsJudItem.getItemStr4Date() != null) {
                    String s1 = DateTimeUtil.dateToStrLong(cmsJudItem.getItemStr4Date());
                    itemStr4DateRS2.setCellValue(s1);
                } else {
                    itemStr4DateRS2.setCellValue("");
                }
                SXSSFCell itemStr11DateRS2 = rowsSheet2.createCell(15);
                if (cmsJudItem.getItemStr11Date() != null) {
                    String s2 = DateTimeUtil.dateToStrLong(cmsJudItem.getItemStr11Date());
                    itemStr11DateRS2.setCellValue(s2);
                } else {
                    itemStr11DateRS2.setCellValue("");
                }
                SXSSFCell itemCreateDateRS2 = rowsSheet2.createCell(16);
                if (cmsJudItem.getItemCreateDate() != null) {
                    String s3 = DateTimeUtil.dateToStrLong(cmsJudItem.getItemCreateDate());
                    itemCreateDateRS2.setCellValue(s3);
                } else {
                    itemCreateDateRS2.setCellValue("");
                }
                SXSSFCell itemStr5RS2 = rowsSheet2.createCell(17);
                itemStr5RS2.setCellValue(cmsJudItem.getItemStr5() == null ? "" :cmsJudItem.getItemStr5().toString());
                SXSSFCell itemStr6RS2 = rowsSheet2.createCell(18);
                itemStr6RS2.setCellValue(cmsJudItem.getItemStr6() == null ? "" :cmsJudItem.getItemStr6().toString());

                CmsJudVolume cmsJudVolume = cmsJudVolumeMapper.selectByPrimaryKey(cmsJudItem.getItmeVolId());
                String volCaseNumByItem = cmsJudVolume.getVolCaseNum() == null ? null : cmsJudVolume.getVolCaseNum().toString();
                SXSSFCell itemVolCaseNum6RS2 = rowsSheet2.createCell(19);
                itemVolCaseNum6RS2.setCellValue(volCaseNumByItem);
            }
        }

        String name="传统案卷"+ DateTimeUtil.getStringDateShort()+".xlsx";
        writeOutStream(request, response, sheets, name);
    }



    /**
     * 单件数据导出
     * @param request
     * @param response
     */
    @RequestMapping("/exportSinDataExcel")
    public void exportSinDataExcel(HttpServletRequest request, HttpServletResponse response,
                                  String sin_itemTitle,String sin_itemType, String sinStatus) {
        CmsSinItemExample cmsSinItemExample = new CmsSinItemExample();
        CmsSinItemExample.Criteria criteria2 = cmsSinItemExample.createCriteria();
        if(StringUtils.isNotBlank(sin_itemTitle)){
            criteria2.andItemTitleLike("%"+sin_itemTitle+"%");
        }
        if(StringUtils.isNotBlank(sin_itemType)){
            criteria2.andItemExp1EqualTo(sin_itemType);
        }
        if(StringUtils.isNotBlank(sinStatus) && "0".equals(sinStatus)){
            criteria2.andItemArcStatusEqualTo(Long.valueOf(sinStatus));
        }else if(StringUtils.isNotBlank(sinStatus) && "1".equals(sinStatus)){
            criteria2.andItemArcStatusEqualTo(Long.valueOf(sinStatus));
        }else if(StringUtils.isNotBlank(sinStatus) && "2".equals(sinStatus)){
            criteria2.andItemArcStatusEqualTo(Long.valueOf(sinStatus));
        }
        cmsSinItemExample.setOrderByClause("\"ITEM_ID\" ASC");
        List<CmsSinItem> cmsSinItems = cmsSinItemMapper.selectByExample(cmsSinItemExample);

        exportSinExcel(request,response,cmsSinItems);
    }


    /**
     * 单件导出工具类
     * @param request
     * @param response
     * @param
     */
    public void exportSinExcel(HttpServletRequest request, HttpServletResponse response,List<CmsSinItem> cmsSinItems) {
        SXSSFWorkbook sheets = new SXSSFWorkbook();

        SXSSFSheet sheet = sheets.createSheet("singlePiece");
        //第一行
        SXSSFRow row0 = sheet.createRow(0);
        SXSSFCell itemId = row0.createCell(0);
        itemId.setCellValue("ID");
        SXSSFCell itemNum = row0.createCell(1);
        itemNum.setCellValue("保管期限");
        SXSSFCell itemName = row0.createCell(2);
        itemName.setCellValue("文号");
        SXSSFCell itemTemporary = row0.createCell(3);
        itemTemporary.setCellValue("单元号");
        SXSSFCell itemSecret = row0.createCell(4);
        itemSecret.setCellValue("密级");
        SXSSFCell itemTitle = row0.createCell(5);
        itemTitle.setCellValue("题名");
        SXSSFCell itemRespons = row0.createCell(6);
        itemRespons.setCellValue("责任者");
        SXSSFCell itemCreateTime = row0.createCell(7);
        itemCreateTime.setCellValue("发文日期");
        SXSSFCell itemPage = row0.createCell(8);
        itemPage.setCellValue("页数");
        SXSSFCell itemExp4 = row0.createCell(9);
        itemExp4.setCellValue("保密年限");
        SXSSFCell itemStatus = row0.createCell(10);
        itemStatus.setCellValue("件状态(0:正常;1:鉴定中;2:借阅中;3:已移出;8:销毁)");
        SXSSFCell itemStr1 = row0.createCell(11);
        itemStr1.setCellValue("件号");
        SXSSFCell itemStr2 = row0.createCell(12);
        itemStr2.setCellValue("原文地址");
        SXSSFCell itemStr3 = row0.createCell(13);
        itemStr3.setCellValue("挂接状态(0:未挂接;1:已挂接)");
        SXSSFCell itemStr4 = row0.createCell(14);
        itemStr4.setCellValue("著录人");

        SXSSFCell itemStr4Date = row0.createCell(15);
        itemStr4Date.setCellValue("著录时间");
        SXSSFCell itemStr1Date = row0.createCell(16);
        itemStr1Date.setCellValue("件归档时间");

        SXSSFCell itemArcPerson = row0.createCell(17);
        itemArcPerson.setCellValue("归档责任人");
        SXSSFCell itemFian = row0.createCell(18);
        itemFian.setCellValue("全宗号");
        SXSSFCell itemCarrier = row0.createCell(19);
        itemCarrier.setCellValue("载体类型(0:仅实体;1:仅电子;2:共存)");
        SXSSFCell itemBoxNumber = row0.createCell(20);
        itemBoxNumber.setCellValue("盒编号");
        SXSSFCell itemStoragelocation = row0.createCell(21);
        itemStoragelocation.setCellValue("存放位置");
        SXSSFCell itemPublicStatus = row0.createCell(22);
        itemPublicStatus.setCellValue("是否公开(0:未公开(默认);1:公开)");
        SXSSFCell itemExp1 = row0.createCell(23);
        itemExp1.setCellValue("文件类型");
        SXSSFCell itemExp2 = row0.createCell(24);
        itemExp2.setCellValue("归档单位");
        SXSSFCell itemExp3 = row0.createCell(25);
        itemExp3.setCellValue("归档年度");
        SXSSFCell itemCreateDate = row0.createCell(26);
        itemCreateDate.setCellValue("创建时间");
        SXSSFCell itemNote = row0.createCell(27);
        itemNote.setCellValue("备注");
        //第二行
        SXSSFRow row2 = sheet.createRow(1);
        SXSSFCell itemIdByTwo = row2.createCell(0);
        itemIdByTwo.setCellValue("itemId");
        SXSSFCell itemNumByTwo = row2.createCell(1);
        itemNumByTwo.setCellValue("itemNum");
        SXSSFCell itemNameByTwo = row2.createCell(2);
        itemNameByTwo.setCellValue("itemName");
        SXSSFCell itemTemporaryByTwo = row2.createCell(3);
        itemTemporaryByTwo.setCellValue("itemTemporary");
        SXSSFCell itemSecretByTwo = row2.createCell(4);
        itemSecretByTwo.setCellValue("itemSecret");
        SXSSFCell itemTitleByTwo = row2.createCell(5);
        itemTitleByTwo.setCellValue("itemTitle");
        SXSSFCell itemResponsByTwo = row2.createCell(6);
        itemResponsByTwo.setCellValue("itemRespons");
        SXSSFCell itemCreateTimeByTwo = row2.createCell(7);
        itemCreateTimeByTwo.setCellValue("itemCreateTime");
        SXSSFCell itemPageByTwo = row2.createCell(8);
        itemPageByTwo.setCellValue("itemPage");
        SXSSFCell itemExp4ByTwo = row2.createCell(9);
        itemExp4ByTwo.setCellValue("itemExp4");
        SXSSFCell itemStatusByTwo = row2.createCell(10);
        itemStatusByTwo.setCellValue("itemStatus");
        SXSSFCell itemStr1ByTwo = row2.createCell(11);
        itemStr1ByTwo.setCellValue("itemStr1");
        SXSSFCell itemStr2ByTwo = row2.createCell(12);
        itemStr2ByTwo.setCellValue("itemStr2");
        SXSSFCell itemStr3ByTwo = row2.createCell(13);
        itemStr3ByTwo.setCellValue("itemStr3");
        SXSSFCell itemStr4ByTwo = row2.createCell(14);
        itemStr4ByTwo.setCellValue("itemStr4");
        SXSSFCell itemStr4DateByTwo = row2.createCell(15);
        itemStr4DateByTwo.setCellValue("itemStr4Date");
        SXSSFCell itemStr1DateByTwo = row2.createCell(16);
        itemStr1DateByTwo.setCellValue("itemStr1Date");
        SXSSFCell itemArcPersonByTwo = row2.createCell(17);
        itemArcPersonByTwo.setCellValue("itemArcPerson");
        SXSSFCell itemFianByTwo = row2.createCell(18);
        itemFianByTwo.setCellValue("itemFian");
        SXSSFCell itemCarrierByTwo = row2.createCell(19);
        itemCarrierByTwo.setCellValue("itemCarrier");
        SXSSFCell itemBoxNumberByTwo = row2.createCell(20);
        itemBoxNumberByTwo.setCellValue("itemBoxNumber");
        SXSSFCell itemStoragelocationByTwo = row2.createCell(21);
        itemStoragelocationByTwo.setCellValue("itemStoragelocation");
        SXSSFCell itemPublicStatusByTwo = row2.createCell(22);
        itemPublicStatusByTwo.setCellValue("itemPublicStatus");
        SXSSFCell itemExp1ByTwo = row2.createCell(23);
        itemExp1ByTwo.setCellValue("itemExp1");
        SXSSFCell itemExp2ByTwo = row2.createCell(24);
        itemExp2ByTwo.setCellValue("itemExp2");
        SXSSFCell itemExp3ByTwo = row2.createCell(25);
        itemExp3ByTwo.setCellValue("itemExp3");
        SXSSFCell itemCreateDateByTwo = row2.createCell(26);
        itemCreateDateByTwo.setCellValue("itemCreateDate");
        SXSSFCell itemNoteByTwo = row0.createCell(27);
        itemNoteByTwo.setCellValue("itemNote");
        if (cmsSinItems.size() != 0) {
            //第三行插入数据
            for (int i = 0; i < cmsSinItems.size(); i++) {
                CmsSinItem cmsSinItem = cmsSinItems.get(i);
                SXSSFRow rows = sheet.createRow(i + 2);

                SXSSFCell itemIdRows = rows.createCell(0);
                itemIdRows.setCellValue(cmsSinItem.getItemId() == null ? "" :cmsSinItem.getItemId().toString());
                SXSSFCell itemNumRows = rows.createCell(1);
                itemNumRows.setCellValue(cmsSinItem.getItemNum() == null ? "" :cmsSinItem.getItemNum().toString());
                SXSSFCell itemNameRows = rows.createCell(2);
                itemNameRows.setCellValue(cmsSinItem.getItemName() == null ? "" :cmsSinItem.getItemName().toString());
                SXSSFCell itemTemporaryRows = rows.createCell(3);
                itemTemporaryRows.setCellValue(cmsSinItem.getItemTemporary() == null ? "" :cmsSinItem.getItemTemporary().toString());
                SXSSFCell itemSecretRows = rows.createCell(4);
                itemSecretRows.setCellValue(cmsSinItem.getItemSecret() == null ? "" :cmsSinItem.getItemSecret().toString());
                SXSSFCell itemTitleRows = rows.createCell(5);
                itemTitleRows.setCellValue(cmsSinItem.getItemTitle() == null ? "" :cmsSinItem.getItemTitle().toString());
                SXSSFCell itemResponsRows = rows.createCell(6);
                itemResponsRows.setCellValue(cmsSinItem.getItemRespons() == null ? "" :cmsSinItem.getItemRespons().toString());
                SXSSFCell itemCreateTimeRows = rows.createCell(7);
                itemCreateTimeRows.setCellValue(cmsSinItem.getItemCreateTime() == null ? "" :cmsSinItem.getItemCreateTime().toString());
                SXSSFCell itemPageRows = rows.createCell(8);
                itemPageRows.setCellValue(cmsSinItem.getItemPage() == null ? "" :cmsSinItem.getItemPage().toString());
                SXSSFCell itemExp4Rows = rows.createCell(9);
                itemExp4Rows.setCellValue(cmsSinItem.getItemExp4() == null ? "" :cmsSinItem.getItemExp4().toString());
                SXSSFCell itemStatusRows = rows.createCell(10);
                itemStatusRows.setCellValue(cmsSinItem.getItemStatus() == null ? "" :cmsSinItem.getItemStatus().toString());
                SXSSFCell itemStr1Rows = rows.createCell(11);
                itemStr1Rows.setCellValue(cmsSinItem.getItemStr1() == null ? "" :cmsSinItem.getItemStr1().toString());
                SXSSFCell itemStr2Rows = rows.createCell(12);
                itemStr2Rows.setCellValue(cmsSinItem.getItemStr2() == null ? "" :cmsSinItem.getItemStr2().toString());
                SXSSFCell itemStr3Rows = rows.createCell(13);
                itemStr3Rows.setCellValue(cmsSinItem.getItemStr3() == null ? "" :cmsSinItem.getItemStr3().toString());
                SXSSFCell itemStr4Rows = rows.createCell(14);
                itemStr4Rows.setCellValue(cmsSinItem.getItemStr4() == null ? "" :cmsSinItem.getItemStr4().toString());
                SXSSFCell itemStr4DateRows = rows.createCell(15);
                if (cmsSinItem.getItemStr4Date() != null) {
                    String s = DateTimeUtil.dateToStr(cmsSinItem.getItemStr4Date());
                    itemStr4DateRows.setCellValue(s);
                } else {
                    itemStr4DateRows.setCellValue("");
                }
                SXSSFCell itemStr1DateRows = rows.createCell(16);
                if (cmsSinItem.getItemStr1Date() != null) {
                    String s = DateTimeUtil.dateToStr(cmsSinItem.getItemStr1Date());
                    itemStr1DateRows.setCellValue(s);
                } else {
                    itemStr1DateRows.setCellValue("");
                }
                SXSSFCell itemArcPersonRows = rows.createCell(17);
                itemArcPersonRows.setCellValue(cmsSinItem.getItemArcPerson() == null ? "" :cmsSinItem.getItemArcPerson().toString());
                SXSSFCell itemFianRows = rows.createCell(18);
                itemFianRows.setCellValue(cmsSinItem.getItemFian() == null ? "" :cmsSinItem.getItemFian().toString());
                SXSSFCell itemCarrierRows = rows.createCell(19);
                itemCarrierRows.setCellValue(cmsSinItem.getItemCarrier() == null ? "" :cmsSinItem.getItemCarrier().toString());
                SXSSFCell itemBoxNumberRows = rows.createCell(20);
                itemBoxNumberRows.setCellValue(cmsSinItem.getItemBoxNumber() == null ? "" :cmsSinItem.getItemBoxNumber().toString());
                SXSSFCell itemStoragelocationRows = rows.createCell(21);
                itemStoragelocationRows.setCellValue(cmsSinItem.getItemStoragelocation() == null ? "" :cmsSinItem.getItemStoragelocation().toString());
                SXSSFCell itemPublicStatusRows = rows.createCell(22);
                itemPublicStatusRows.setCellValue(cmsSinItem.getItemPublicStatus() == null ? "" :cmsSinItem.getItemPublicStatus().toString());
                SXSSFCell itemExp1Rows = rows.createCell(23);
                itemExp1Rows.setCellValue(cmsSinItem.getItemExp1() == null ? "" :cmsSinItem.getItemExp1().toString());
                SXSSFCell itemExp2Rows = rows.createCell(24);
                itemExp2Rows.setCellValue(cmsSinItem.getItemExp2() == null ? "" :cmsSinItem.getItemExp2().toString());
                SXSSFCell itemExp3Rows = rows.createCell(25);
                itemExp3Rows.setCellValue(cmsSinItem.getItemExp3() == null ? "" :cmsSinItem.getItemExp3().toString());
                SXSSFCell itemCreateDateRows = rows.createCell(26);//创建时间
                if (cmsSinItem.getItemCreateDate() != null) {
                    String s = DateTimeUtil.dateToStr(cmsSinItem.getItemCreateDate());
                    itemCreateDateRows.setCellValue(s);

                } else {
                    itemCreateDateRows.setCellValue("");
                }
                SXSSFCell itemNoteRows = row0.createCell(27);
                itemNoteRows.setCellValue(cmsSinItem.getItemNote()==null ? "" : cmsSinItem.getItemNote().toString());
            }
        }
        String name="简化案卷(单件)"+ DateTimeUtil.getStringDateShort()+".xlsx";
        writeOutStream(request, response, sheets, name);
    }
}
